/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ContentManagers.RoleManagers;

import ContentManagers.Security.Authenticator;
import ContentManagers.Models.AccountingManager;
import ContentManagers.Security.Validator;
import DBConnection.ConnectionFactory;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import ContentManagers.Security.Validator;

/**
 *
 * @author Ervin James
 */
public class AMManager {
    
    private Calendar getCurrentDate()
    {
        Calendar date = Calendar.getInstance();
        
        return date;
    }
    
    private Calendar getSpecifiedDate(String date)
    {
        SimpleDateFormat dateformatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        Calendar cal = Calendar.getInstance();
        try {
            Date sDate = dateformatter.parse(date);   
            cal.setTime(sDate);
        } catch (ParseException ex) {
            Logger.getLogger(AMManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return cal;
    }
    
    private long getHours(Calendar date1, Calendar date2)
    {
        long diff = date1.getTimeInMillis() - date2.getTimeInMillis();
        
        long hours = diff / (1000 * 60 * 60);
        
        return hours;
    }
    
    public boolean isPasswordExpired(AccountingManager am)
    {
         boolean result = false;

        try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory("manager");
            Connection conn = myFactory.getConnection();

            int i = 1;

            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM am_accounts WHERE username = ?");

            pstmt.setString(i++, am.getUsername());
                     
            ResultSet rs = pstmt.executeQuery();

            while(rs.next())
            {  
                if(getHours(getCurrentDate(), getSpecifiedDate(rs.getString("password_timestamp"))) > 24)          
                    result = true;
                //System.out.println(getHours(getCurrentDate(), getSpecifiedDate(rs.getString("password_timestamp"))));
            }
            
            conn.close();
        }
        catch(SQLException e)
        {
           e.printStackTrace();
        }

        return result;  
    }
    
    public boolean authenticateAM (AccountingManager am)
    {
        
        boolean result = false;

        try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory("manager");
            Connection conn = myFactory.getConnection();

            int i = 1;

            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM am_accounts WHERE username = ?");

            pstmt.setString(i++, am.getUsername());
                     
            ResultSet rs = pstmt.executeQuery();

            while(rs.next())
            {
                if(Authenticator.CompareHash(am.getPassword(), rs.getString("password"), rs.getString("password_salt")))
                    result = true;
               
            }
            
            conn.close();
        }
        catch(SQLException e)
        {
           e.printStackTrace();
        }

        return result;  
    }
    
    public ResultSet salesPerProductType(String product_type)
    {
            try {
            //get connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory("manager");
            Connection conn = myFactory.getConnection();

            int i = 1;
            //Construct SQL Query
            PreparedStatement pstmt;
            
            if(product_type.equals("0") == true)
                pstmt = conn.prepareStatement("SELECT * from sales_per_product_type ORDER by type_total_sales DESC");
            else
            {
                pstmt = conn.prepareStatement("SELECT * from sales_per_product_type WHERE product_type_name = ?");
                pstmt.setString(i++, Validator.inputSanitizer(product_type));
            }
            //execute SQL statement
            ResultSet result = pstmt.executeQuery();

            return result;

            }
         catch (SQLException ex) {ex.printStackTrace();return null;} 
    }
    
    public ResultSet salesPerProduct(String product_type, boolean top3)
    {
            try {
            //get connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory("manager");
            Connection conn = myFactory.getConnection();

            int i = 1;
            //Construct SQL Query
            PreparedStatement pstmt;
            
            if(top3 == false)
                pstmt = conn.prepareStatement("SELECT * from sales_per_product WHERE product_type_name = ?");
            else
                pstmt = conn.prepareStatement("SELECT * from sales_per_product WHERE product_type_name = ? LIMIT 3");
            pstmt.setString(i++, Validator.inputSanitizer(product_type));
            
            
            //execute SQL statement
            ResultSet result = pstmt.executeQuery();

            return result;

            }
         catch (SQLException ex) {ex.printStackTrace();return null;} 
    }
    
    public ResultSet totalSales(String product_type)
    {
            try {
            //get connection
            ConnectionFactory myFactory = ConnectionFactory.getFactory("manager");
            Connection conn = myFactory.getConnection();

            int i = 1;
            //Construct SQL Query
            PreparedStatement pstmt;
            
            pstmt = conn.prepareStatement("SELECT SUM(purchase_value) FROM sales");
            
            //execute SQL statement
            ResultSet result = pstmt.executeQuery();

            return result;
            }
            catch (SQLException ex) {ex.printStackTrace();return null;} 
    }
    
}
